# 表关系：一对一 一对多 多对多
import pymysql
connect = pymysql.connect(
    host='127.0.0.1',
    db='study',
    user='root',
    password='mysql',
    port=3306,
    charset='utf8', )
# 创建游标
cur = connect.cursor()


# 1.一对一：关键字：foreign key + unique 在两张表中任意一张表中设置unique约束
sql_woman = """
    create table  woman(
      IDCard char(2) primary key,
      name  varchar(4) not null,
      money float unsigned default NULL
    )
"""
sql_man = """
    create table man(
      IDCard char(2) primary key,
      name  varchar(4) not null,
      money float unsigned default NULL,
      foreign  key (IDCard) REFERENCES WOMAN (IDCard)
    )
"""
cur.execute(sql_woman)
cur.execute(sql_man)

# 2.一对多 关键字：foreign key
sql_teacher = """
    create table teachers(
        cno char(2) primary key,
        name varchar(3) not null,
        sex char(1) default '男',
        age int(10),
        namenumber char(4) not null,
        foreign key (namenumber) references  students (cno) 
    )
"""
sql_student = """
    create table students(
        cno char(4) primary key,
        name varchar(4) not null,
        sex char(1) default '男',
        age int(10)
    )
"""
cur.execute(sql_student)
cur.execute(sql_teacher)

# 3.多对多：添加第三张表


